CREATE TABLE [dbo].[PromotionReview](
	[PromotionReviewId] [int] IDENTITY(1,1) NOT NULL,
	[PromotionId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[ReviewText] [varchar](max) NOT NULL,
	[Rating] [int] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_PromotionReview] PRIMARY KEY CLUSTERED 
(
	[PromotionReviewId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PromotionReview]  WITH CHECK ADD  CONSTRAINT [FK_PromotionReview_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
GO
ALTER TABLE [dbo].[PromotionReview] CHECK CONSTRAINT [FK_PromotionReview_Promotion]
GO
ALTER TABLE [dbo].[PromotionReview]  WITH CHECK ADD  CONSTRAINT [FK_PromotionReview_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[PromotionReview] CHECK CONSTRAINT [FK_PromotionReview_User]
GO
ALTER TABLE [dbo].[PromotionReview] ADD  CONSTRAINT [DF_PromotionReview_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO
CREATE TABLE [dbo].[PromotionLike](
	[PromotionId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[IsLiked] [bit] NOT NULL,
 CONSTRAINT [PK_PromotionLike] PRIMARY KEY CLUSTERED 
(
	[PromotionId] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PromotionLike]  WITH CHECK ADD  CONSTRAINT [FK_PromotionLike_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
GO
ALTER TABLE [dbo].[PromotionLike] CHECK CONSTRAINT [FK_PromotionLike_Promotion]
GO
ALTER TABLE [dbo].[PromotionLike]  WITH CHECK ADD  CONSTRAINT [FK_PromotionLike_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[PromotionLike] CHECK CONSTRAINT [FK_PromotionLike_User]
GO
CREATE PROCEDURE spAPI_PromotionReview_GetList
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_CheckToken @Token = @Token, @Result = @IdResult OUT
	
	IF @IdResult = 1
	BEGIN
		if @returnAsDataset = 0
		begin
			DECLARE @Likes INT, @Dislikes INT
			
			SELECT @Likes = SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END),
				@Dislikes = SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END)
			FROM PromotionLike
			WHERE PromotionId = @PromotionId
					
			SELECT @Result = (SELECT *
							FROM (SELECT 1 AS Tag, NULL AS Parent, @Likes AS [Reviews!1!Likes], @Dislikes AS [Reviews!1!Dislikes],
									NULL AS [Review!2!UserName!ELEMENT], NULL AS [Review!2!Rating!ELEMENT], NULL AS [Review!2!Text!ELEMENT],
									NULL AS [Review!2!UserId!ELEMENT]
								
								UNION ALL
								
								SELECT 2 AS Tag, 1 AS Parent, NULL, NULL,
									[User].[Login], 
									CONVERT(VARCHAR(20), PromotionReview.Rating) + ',' + 
										CASE WHEN PromotionReview.Rating BETWEEN 1 AND 2 THEN 'Y' 
											WHEN PromotionReview.Rating BETWEEN 3 AND 4 THEN 'G' 
										ELSE 'R' END,
									PromotionReview.ReviewText, PromotionReview.UserId
								FROM PromotionReview (nolock) LEFT JOIN [User] (nolock) ON PromotionReview.UserId = [User].UserId
								WHERE PromotionReview.PromotionId = @PromotionId) AS vw
							FOR XML EXPLICIT)
		end
		else
		begin
			SELECT
				[User].[Login] as UserName,
				PromotionReview.Rating,
				PromotionReview.[PromotionReviewId],
				PromotionReview.[PromotionId],
				PromotionReview.[UserId],
				PromotionReview.[ReviewText],
				PromotionReview.[CreatedDate]
      		FROM PromotionReview (nolock) LEFT JOIN [User] (nolock) ON PromotionReview.UserId = [User].UserId
			WHERE PromotionReview.PromotionId = @PromotionId
			
			return
		end
	END					
	
	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
CREATE PROCEDURE spAPI_PromotionReview_Add
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@ReviewText VARCHAR(MAX),
	@Rating INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		INSERT INTO PromotionReview
		(PromotionId, UserId, ReviewText, Rating)
		VALUES (@PromotionId, @UserId, @ReviewText, @Rating)
		
	SELECT @IdResult AS IdResult
END
GO
CREATE PROCEDURE spAPI_Promotion_Like
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		IF NOT EXISTS(SELECT 1 FROM PromotionLike WHERE PromotionId = @PromotionId AND UserId = @UserId)
			INSERT INTO PromotionLike
			(PromotionId, UserId, IsLiked)
			VALUES (@PromotionId, @UserId, 1)
		ELSE
			UPDATE PromotionLike
			SET IsLiked = 1
			WHERE (PromotionId = @PromotionId) 
				AND (UserId = @UserId)
		
	SELECT @IdResult AS IdResult
END
GO
CREATE PROCEDURE spAPI_Promotion_Dislike
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		IF NOT EXISTS(SELECT 1 FROM PromotionLike WHERE PromotionId = @PromotionId AND UserId = @UserId)
			INSERT INTO PromotionLike
			(PromotionId, UserId, IsLiked)
			VALUES (@PromotionId, @UserId, 0)
		ELSE
			UPDATE PromotionLike
			SET IsLiked = 0
			WHERE (PromotionId = @PromotionId) 
				AND (UserId = @UserId)
		
	SELECT @IdResult AS IdResult
END
GO





